
*Sorting District-Level Census Aggregate Value and Age 5-17*
use census1980.dta, clear

keep if SUMRYLVL==40
rename FIPS statefip
rename NCESID ncesid
gen pop5_17= pop5years+pop6years+pop7to9years+pop10to13years+pop14years+pop15years+pop16years+pop17years
replace aggregatevalue=aggregatevalue*250
gen hv1980= aggregatevalue/pop5_17
sum hv1980 if statefip==21
keep if statefip==21
keep ncesid aggregatevalue pop5_17 hv1980
save distagghv1980.dta, replace

*Grabbing Harlan Independent 1980 HV*
keep if ncesid==2102520
rename aggregatevalue harlanindagghv
rename pop5_17 harlanindpop5_17
joinby ncesid using kydistcntycrosswalk.dta, unmatched(master)
tab _merge
drop _merge
save harlanindagghv1980.dta, replace

*Sorting County-Level Census Home Value Data for 1980, 1990, 2000*
import excel using HSG03.xls, firstrow sheet(HSG03F) clear

save cntycensushv1.dta, replace

clear

import excel using HSG03.xls, firstrow sheet(HSG03G)

save cntycensushv2.dta, replace

clear

import excel using HSG03.xls, firstrow sheet(HSG03H)

save cntycensushv3.dta, replace

clear

import excel using HSG03.xls, firstrow sheet(HSG03I)

save cntycensushv4.dta, replace

clear

import excel using HSG03.xls, firstrow sheet(HSG03J)

save cntycensushv5.dta, replace

clear

use cntycensushv1.dta

forval y=2/5 {

joinby STCOU using cntycensushv`y'.dta, unmatched(master)
tab _merge
drop _merge
}

gen statefip=substr(STCOU,1,2)
gen county=substr(STCOU,3,5)
gen countyfip=statefip+county
destring statefip, replace
destring countyfip, replace

drop if countyfip==0

*Calculating Aggregate Home Value by Multiplying # of Households in Each Value Bracket by Midpoint Value*

gen aggregatevalue1980=HSG500180D*20000+HSG510180D*((20000+29999)/2)+HSG520180D*((30000+39999)/2)+HSG530180D*((40000+49999)/2)+HSG540180D*((50000+99999)/2)+HSG550180D*((100000+149999)/2)+HSG560180D*((150000+199999)/2)+HSG570180D*200000

gen aggregatevalue1990=HSG500190D*20000+HSG510190D*((20000+29999)/2)+HSG520190D*((30000+39999)/2)+HSG530190D*((40000+49999)/2)+HSG540190D*((50000+99999)/2)+HSG550190D*((100000+1499999)/2)+HSG560190D*((150000+199999)/2)+HSG580190D*((200000+299999)/2)+HSG590190D*((300000+399999)/2)+HSG600190D*((400000+499999)/2)+HSG610190D*500000

gen aggregatevalue2000=HSG505200D*20000+HSG515200D*((20000+29999)/2)+HSG525200D*((30000+39999)/2)+HSG535200D*((40000+49999)/2)+HSG545200D*((50000+99999)/2)+HSG555200D*((100000+1499999)/2)+HSG565200D*((150000+199999)/2)+HSG585200D*((200000+299999)/2)+HSG595200D*((300000+399999)/2)+HSG605200D*((400000+499999)/2)+HSG625200D*((500000+749999)/2)+HSG635200D*((750000+999999)/2)+HSG645200D*1000000

keep if statefip==21

keep statefip countyfip aggregatevalue1980 aggregatevalue1990 aggregatevalue2000

reshape long aggregatevalue, i(countyfip) j(year)

sum aggregatevalue if year==1980

sum aggregatevalue if year==1990

sum aggregatevalue if year==2000

forval y=1/5 {

erase cntycensushv`y'.dta

}

xtset countyfip year

save cntycensushv.dta, replace

clear

*Sorting County-Level Age 5-17 for Census Home Value Measure*
import excel using AGE01.xls, firstrow sheet(Sheet7)

gen statefip=substr(STCOU,1,2)
gen county=substr(STCOU,3,5)
gen countyfip=statefip+county
destring statefip, replace
destring countyfip, replace

drop if countyfip==0

rename AGE180180D p1980

rename AGE180190D p1990

rename AGE180200D p2000

keep if statefip==21

keep countyfip p1980 p1990 p2000

reshape long p, i(countyfip) j(year)

rename p pop5_17

save cntypop5_17.dta, replace

*Joining with County Census Home Value*

joinby countyfip year using cntycensushv.dta, unmatched(master)
tab _merge
drop _merge

gen ppagghv=aggregatevalue/pop5_17

joinby year using deflator19802020.dta, unmatched(master)
drop _merge

gen realppagghv=ppagghv/deflator

xtset countyfip year

save cntycensushv.dta, replace

keep if year==1980

keep countyfip aggregatevalue pop5_17 realppagghv

rename realppagghv hv1980

egen hv1980_pctile=xtile(hv1980) if countyfip>21000, nq(100)

egen hv1980_quartile=xtile(hv1980) if countyfip>21000, nq(4)

label var hv1980_pctile "Pctile of 1980 Census Per-Pupil Aggregate Self-Rep Value"

label var hv1980_quartile "Quartile of 1980 Census Per-Pupil Aggregate Self-Rep Value"

save hv1980.dta, replace


*Adding HV for Mulhenberg, Harlan, Harrison to District-Level HV 1980*

keep if (countyfip==21097|countyfip==21177|countyfip==21095)
gen ncesid=.
replace ncesid=2100081 if countyfip==21177
replace ncesid=2102580 if countyfip==21097
replace ncesid=2102540 if countyfip==21095
drop hv1980_pctile
joinby countyfip using harlanindagghv1980, unmatched(master)
tab _merge
drop _merge
gen harlancoagghv= aggregatevalue- harlanindagghv
gen harlancopop5_17= pop5_17- harlanindpop5_17
gen harlancohv1980=harlancoagghv/harlancopop5_17
replace hv1980= harlancohv1980 if ncesid==2102540
keep ncesid pop5_17 aggregatevalue hv1980
order ncesid
save missingdisthv1980.dta, replace

use distagghv1980.dta

append using missingdisthv1980.dta

sort ncesid

egen hv1980_pctile=xtile(hv1980), nq(100)

egen hv1980_quartile=xtile(hv1980), nq(4)

save distagghv1980.dta, replace

erase missingdisthv1980.dta

*County Population*
import excel using kycountypop19871998.xlsx, cellrange(A6:N127) firstrow clear
foreach var of varlist _all {
local label : variable label `var'
local new_name = lower(strtoname("`label'"))
rename `var' `new_name'
}
rename (_*) (p*)

rename geofips countyfip

rename geoname countyname

destring countyfip, replace

reshape long p, i(countyfip) j(year)

rename p pop

drop if countyfip==21000

save kycountypop19871998.dta, replace

clear
